

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 */
public class DBSalesOrderProduct implements IFDBSalesOrderProduct {
    private  Connection con;
    
    public DBSalesOrderProduct() {
        con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList findSalesproduct(String ssn)
    {
        ArrayList worksonObjects = new ArrayList();
        worksonObjects = miscWhere("essn = '" + ssn + "'",true);
        return worksonObjects;
    }

    public void insertNew(SalesOrderProduct salesOrderProduct)
    {

       int rc = -1;
	   String query="INSERT INTO SalesOrderProduct(salesOrderId, productId, amount)  VALUES(" +
		   salesOrderProduct.getSalesOrder().getId() + "," +
		   salesOrderProduct.getProduct().getId() + "," +
		   salesOrderProduct.getAmount() +
		   ")";


      System.out.println("insert : " + query);
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in SalesOrder db: "+ex);
	   }
	   
    }


    private ArrayList miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrderProduct> list = new ArrayList<SalesOrderProduct>();

		String query =  buildQuery(wClause);
                System.out.println("DBSalesOrderProduct" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		 System.out.println("DBSalesOrderProduct 2" );
			int snr=0;
			while( results.next() ){
		     	 SalesOrderProduct worksObj = new SalesOrderProduct();
			 worksObj = buildWorksOn(results);
                         list.add(worksObj);
			}//end while
			stmt.close();
                        if(retrieveAssociation)
                        {
                            DBSalesOrder dbSalesOrder = new DBSalesOrder();
                            for(SalesOrderProduct wobj : list){
                               int pnumber = wobj.getSalesOrder().getId();
                               SalesOrder pobj = dbSalesOrder.findSalesOrder(pnumber, false);
                               wobj.setSalesOrder(pobj);
                            }

                        }

		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    private SalesOrderProduct buildWorksOn(ResultSet results)
    {
        SalesOrderProduct wobj = new SalesOrderProduct();
        Product empobj = new Product();
        SalesOrder projobj = new SalesOrder();
        wobj.setProduct(empobj); //make reference to employee
        wobj.setSalesOrder(projobj); //make reference to project
         try{
               empobj.setId(results.getInt(1));
               projobj.setId(results.getInt(2));
               projobj.setAmount(results.getDouble(3));
          }
         catch(Exception e)
         {
             System.out.println("building workson object");
         }

        return wobj;

    }
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM works_on";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
}
